package org.corolla.cloth.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.corolla.cloth.entity.Order;


public class DataBaseHelper{
	
	private static Connection conn = null;	// 创建一个数据库连接
	private static String url = "jdbc:oracle:thin:@localhost:1521:XE";		//127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名
	private static String user = "corolla";		//用户名,系统默认的账户名
	private static String password = "corolla";	//你安装时选设置的密码
    
	static{
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver") ;   	// 加载驱动程序   
			conn = DriverManager.getConnection(url, user, password);	//获取连接
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 保存数据
	 * @author ZJD
	 */
	public static void save(Order order){
        String sql = "insert into cs_order(id,cloth_style,cloth_size,mount,unit_price,total_price) " +
		"values(COROLLA.MYSEQ.nextval,?,?,?,?,?)";
        try {
        	PreparedStatement pretmt = conn.prepareStatement(sql);
        	pretmt.setString(1, order.getClothStyle());
        	pretmt.setString(2, order.getClothSize());	        
        	pretmt.setInt(3, order.getClothMount());
        	pretmt.setDouble(4, order.getUnitPrice());
        	pretmt.setDouble(5, order.getTotalPriceBySize());
        	pretmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * 移除数据
	 * @author ZJD
	 */
	public static void removeRecordByPk(int pk){
		try {
			Statement stm = conn.createStatement();
			String sql = "delete from cs_order where id="+pk;
	        stm = conn.createStatement();
	        stm.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
	}
	
	/**
	 * 查询
	 * @author ZJD
	 */
	public static Order findRecordByPk(int pk){
		//columns: id,cloth_style,cloth_size,mount,unit_price,total_price
		List<Order> list = new ArrayList<Order>();
		try {
			Statement stm = conn.createStatement();
			String sql = "select * from cs_order where id="+pk;
			stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			Order order = null;
			while(rs.next()){
				order = new Order();
				order.setId(rs.getInt("id"));
				order.setClothStyle(rs.getString("cloth_style"));
				order.setClothSize(rs.getString("cloth_size"));
				order.setClothMount(rs.getInt("mount"));
				order.setUnitPrice(rs.getDouble("unit_price"));
				order.setTotalPriceBySize(rs.getDouble("total_price"));
				list.add(order);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list.get(0);
	}
	
	
	/**
	 * 按照PKS查询
	 * @author ZJD
	 */
	public static List<Order> findRecordByPks(int[] pks){
		//columns: id,cloth_style,cloth_size,mount,unit_price,total_price
		List<Order> list = new ArrayList<Order>();
		String in_clause="id";
		if(pks.length<=1){
			in_clause=in_clause+"="+pks[0];
		}else{
			in_clause=in_clause+" in (";
			for(int i=0;i<pks.length;i++){
				in_clause=in_clause+pks[i]+",";
			}
			in_clause = in_clause.substring(0, in_clause.length()-1)+")";
		}
		try {
			Statement stm = conn.createStatement();
			String sql = "select * from cs_order where "+in_clause;
			stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			Order order = null;
			while(rs.next()){
				order = new Order();
				order.setId(rs.getInt("id"));
				order.setClothStyle(rs.getString("cloth_style"));
				order.setClothSize(rs.getString("cloth_size"));
				order.setClothMount(rs.getInt("mount"));
				order.setUnitPrice(rs.getDouble("unit_price"));
				order.setTotalPriceBySize(rs.getDouble("total_price"));
				list.add(order);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 查询所有的订单
	 * @author ZJD
	 */
	public static List<Order> findAllRecords(){
		//columns: id,cloth_style,cloth_size,mount,unit_price,total_price
		List<Order> list = new ArrayList<Order>();
		try {
			Statement stm = conn.createStatement();
			String sql = "select * from cs_order ";
			stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			Order order = null;
			while(rs.next()){
				order = new Order();
				order.setId(rs.getInt("id"));
				order.setClothStyle(rs.getString("cloth_style"));
				order.setClothSize(rs.getString("cloth_size"));
				order.setClothMount(rs.getInt("mount"));
				order.setUnitPrice(rs.getDouble("unit_price"));
				order.setTotalPriceBySize(rs.getDouble("total_price"));
				list.add(order);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 修改订单数量
	 * @param pks 主键的数组
	 * @param order 修改过的订单
	 * @author ZJD
	 */
	public static void upadteRecordByPk(Order order){
		String sql = "update cs_order set mount=? ,total_price=? where id=?";
		try {
			PreparedStatement pretmt = conn.prepareStatement(sql);
			pretmt.setInt(1, order.getClothMount());
			pretmt.setDouble(2, order.getTotalPriceBySize());
			pretmt.setInt(3, order.getId());
			pretmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
//		String sql = "update cs_order set mount=1 ,total_price=100.0 where id=18";
//		try {
//			Statement stm  = conn.createStatement();
//			stm.executeUpdate(sql);
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
	}
	
	

}
